﻿USE [Feidu]
GO
/****** Object:  StoredProcedure [dbo].[m_SelectPagingRecord]    Script Date: 10/29/2010 13:13:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--region [m_SelectPagingRecord]

------------------------------------------------------------------------------------------------------------------------
-- 创建标识:  MaoCaiJun.DataBase 代码辅助工具  (毛才君)       2010/6/27 15:12:49
-- 存储过程:  [m_SelectPagingRecord]
------------------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[m_SelectPagingRecord_1]
    @PageSize int=10 ,							--每页显示的记录数
    @PageIndex int=1 ,							--当前要显示的页号
    @keyfield varchar(100)='' ,					--主键名或者标识列名
    @fieldlist varchar(MAX)='*', 				--select子句，不包含select关键字,如：*或者Id,UserId,UserName等。
    @tablename varchar(MAX)='', 				--from子句，不包含from关键子，如：myTable或者myTable,yourTable
    @strwhere varchar(MAX)='', 				--Where子句，不包含where关键字，如空的，或者 id>2 等
    @orderfield varchar(MAX)='',				--order by 子句，不包含order by 子句 ，如id desc,UserId asc 等
    @groupfield varchar(MAX)='',				--group by 子句，不包含order by 子句 ，如id desc,UserId asc 等    
    @IsReCount bit = 0,           				-- 返回记录总数, 非 0 值则返回记录总数
    @CountRows int=0 output, 					--返回记录总数
    @CountPage int=0 output 					--返回总页数
as
--------处理SQL中危险字符,并且将条件处理成易嵌入的形式 
    --set @strwhere=replace(@strwhere,'''','''''') 
    set @strwhere=replace(@strwhere,'--','') 
    set @strwhere=replace(@strwhere,';','') 
    --  if @PageIndex=0; 
    --set @PageIndex=1; 
--------定义局部变量-----------------
    declare @Id1 varchar(20),@Id2 varchar(20) 	--开始和结束的记录号
    declare @OrderBySqls varchar(MAX) 			--order by 子句
    declare @GroupBySqls varchar(MAX) 			--group by 子句
    declare @WhereSqls varchar(MAX) 			--where 子句
    declare @Sqls nvarchar(MAX) 				--最终组合成的Sqls语句
    declare @TmpStr varchar(MAX) 				--临时
    declare @Portion  nvarchar(MAX)
--------order by--------------------
    if @orderfield <> ''
        set @OrderBySqls = ' order by '+@orderfield
    else
        set @OrderBySqls = ''
--------group by--------------------
    if @groupfield <> ''
        set @GroupBySqls = ' group by '+@groupfield
    else
        set @GroupBySqls = ''
--------where ----------------------
    if @strwhere <> ''
        set @WhereSqls = ' where ('+@strwhere+')'
    else
        set @WhereSqls = ''
--------
    set @TmpStr = @WhereSqls
    if @IsReCount != 0
        goto LabelRes
--------如果显示第一页，可以直接用top来完成
    if @PageIndex<=1
    begin
        select @Id1=cast(@PageSize as varchar(20))
        if @PageSize=0
            set @Sqls = ('select '+@fieldlist+' from '+@tablename+''+@WhereSqls+@GroupBySqls+@OrderBySqls)
        else
            set @Sqls = ('select top '+@Id1+' '+@fieldlist+' from '+@tablename+''+@WhereSqls+@GroupBySqls+@OrderBySqls)
        exec sp_executesql @Sqls
        --print @Sqls
        goto LabelRes
    end
--------不是第一页
	--print ((@PageIndex-1)*@PageSize)+1
	if (@orderfield='' or @orderfield=NULL)
		set @orderfield=@keyfield
    set @Sqls = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @fieldlist + ' from ' + @TableName + @WhereSqls +@GroupBySqls
    set @Sqls = @Sqls + ') as t where rowId between ' + Convert(varchar(50),((@PageIndex-1)*@PageSize)+1) + ' and ' +  Convert(varchar(50),@PageIndex*@PageSize)
    exec sp_executesql @Sqls
    --print @Sqls
--print @Sqls
-----------
    LabelRes:
--------返回总记录数
	if @groupfield <> ''
        set @Sqls = 'select @a=count(1) from (select '+@groupfield+' from '+@tablename+''+@TmpStr+@GroupBySqls+') as t'
    else
		set @Sqls = 'select @a=count(1) from '+@tablename+''+@TmpStr
    exec sp_executesql @sqls,N'@a int output',@CountRows output
    --print @Sqls
--------返回总页数
    if @CountRows <= @PageSize
        set @CountPage = 1
    else if @PageSize=0
        set @CountPage=0
    else
    begin
        set @CountPage = @CountRows/@PageSize
        if (@CountRows%@PageSize) > 0
            set @CountPage = @CountPage + 1
    end
    print @CountPage
    print @CountRows
    return
--endregion


